<?

# Insert statements
$userInsert = $db->prepare("INSERT INTO users VALUES (?, ?, ?, ?)");
$passwordInsert = $db->prepare("INSERT INTO passwords VALUES (?, ?, ?)");

$linksInsert = $db->prepare("INSERT INTO links VALUES (?, ?)");
$tagsInsert = $db->prepare("INSERT INTO tags VALUES (?, ?, ?)");

$userLinksInsert = $db->prepare("INSERT INTO userLinks VALUES (?, ?, ?)");

# login querys
$loginQuery = $db->prepare("SELECT * from passwords where hashP = ':hash' and userID = (SELECT id FROM users where name = ':login')");
#$loginQuery = $db->prepare("SELECT count(*) from users");

# register querys
$retrieveUser = $db->prepare("SELECT id from users where name = ?");
$retrieveUserfromEmail = $db->prepare("SELECT name from users where email = ?");

# duplicate tag query
$dupTag = $db->prepare("SELECT count(*) from tags where name = ?");

# search tags by user
# Add Link feature
/*
	Insert Link in DB:
		1. insert into links table the link value.
		2. insert into tags table all the indicated tags and also filling the linkTags table.
		3. insert into userLinks table the correct value.
*/
$db_insert_link = $db->prepare("INSERT INTO links VALUES (?, ?, ?, ?, 1)");
$db_select_link_id = $db->prepare("SELECT id FROM links WHERE linkValue = ?");
$db_select_link_user = $db->prepare("SELECT linkID FROM links WHERE userID = ?");
$db_insert_tag = $db->prepare("INSERT INTO tags VALUES (?, ?, ?)");
$db_select_tag_id = $db->prepare("SELECT id FROM tags WHERE name = ?");
$db_select_tag_name = $db->prepare("SELECT name FROM tags WHERE id = ?");
$db_insert_linkTag = $db->prepare("INSERT INTO linksTags VALUES (?, ?, ?)");
$db_insert_userLink = $db->prepare("INSERT INTO userLinks VALUES (?, ?, ?)");

# retrieve tags
	# by user
	$db_retrieve_tags_user = $db->prepare(" select id, name from tags where id in (select tagsID from linksTags where linkID in (select linkID from userLinks where userID = (select id from users where name = ?)))");
	# counting number of links with Y tag
	$db_retrieve_tags_count = $db->prepare("select count(*) as total from linksTags where linkID in (select linkID from userLinks where userID = (select id from users where name = :name)) and tagsID = :tagID group by tagsID");
	# by linkID
	$db_retrieve_tags_by_id = $db->prepare("select tags.name from tags, linksTags where tags.id = linksTags.tagsID and linksTags.linkID = ?");

# retrieve links
	# by user
	$db_retrieve_link_user = $db->prepare("select * from links where id in (select linkID from userLinks where userID = (select id from users where name = :name)) group by linkValue");
	# by tag and user
	$db_retrieve_link_tag = $db->prepare("select * from links where id in (select linkID from linksTags where linkID in (select linkID from userLinks where userID = (select id from users where name = :name)) and tagsID = :tagID);");
	# by tag
	$db_retrieve_link_tags = $db->prepare("select * from links where id in (select linkID from linksTags where tagsID = ?)");
	# recent
	$db_retrieve_link_recent = $db->prepare("select * from links order by dataA");
	# featured
	$db_retrieve_link_featured = $db->prepare("select links.id,dataA,linkValue,description from links CROSS JOIN userLinks on userLinks.linkID = links.id group by linkID order by count(linkID) DESC");
	# user and link id
	$db_user_has_link = $db->prepare("select links.id from users,links where users.name = ? and links.id = ?");
	
# retrieve user id, name
	# by link
	$db_retrieve_user_link = $db->prepare(" select distinct(users.name) as name from users, userLinks where users.id = userLinks.userID and userLinks.linkID = ?");
	# by tag
	$db_retrieve_user_tag  = $db->prepare("select users.name, group_concat(tags.name, ';') from users, linksTags, userLinks, tags where users.id = ? and users.id = userLinks.userID and userLinks.linkID = linksTags.linkID and linksTags.tagsID = tags.id;");
	#
	$db_show_users = $db->prepare("select users.name from users");
	
# update link
$db_click_link = $db->prepare("UPDATE links set hits = hits + 1 where links.id = ?");

function insertLink($link) {
	global $db_insert_link;
	global $db_select_link;
	global $db_select_link_id;
	global $retrieveUser;
	global $_SESSION;
	global $db_insert_userLink;
	global $db_insert_linkTag;
	global $db_insert_tag;
	global $db_select_tag_id;
	
	$db_insert_link->execute(array(NULL, $link['dataA'], $link['linkValue'], $link['description']));
	
	$link_id = $db_select_link_id->execute(array($link['linkValue']));
	$user_id = $retrieveUser->execute(array($_SESSION['user']));
	
	$db_insert_userLink->execute(array(NULL, $user_id, $link_id));
	
	foreach($link['tags'] as $tag) {
		$db_insert_tag->execute(array(NULL, $tag['name'], $tag['description']));
		$db_insert_linkTag->execute(array(NULL, $link_id, $db_select_tag_id->execute($tag)));
	}
}

# query format is:
	# 0 - initialQuery
	# 1 - variable appending
	# 2 - finalQuery
	
$queryOr = " OR ";
$queryAnd = " AND ";
$queryExclusive = array(
	$queryOr,
	$queryAnd);

$queryIntersect = " INTERSECT ";
#$queryBasic = "select links.id, links.dataA, links.linkValue, links.description, links.hits, tags.ID, tags.name from links,tags, linksTags where tags.id = linksTags.tagsID and linksTags.linkID = links.id group by tags.name";
$queryBasic = "select links.id, links.dataA, links.linkValue, links.description, links.hits from links";

$queryTag = array(
	"select id from links where id in (select linkID from linksTags where tagsID in (select id from tags where ",
	" name = ",
	" ))",
	);
	
$queryUser = array(
	"select id from links where id in (select linkID from userLinks where userID in (select id from users where ",
	" name = ",
	")) group by linkValue", # solo user
	")) group by linkValue" # w tags
	);
	
$queryIdToLink = array(
	"select * from links where id in ( ",
	"",
	""
	);
	
$querys = array(
	'tag'  => $queryTag,
	'user' => $queryUser,
	'man' => $queryIdToLink
	);


$queryType = "";

$orderFeatured = " order by hits DESC";
$orderRecent   = " order by dataA";

$orderQuery = array(
	'featured' => $orderFeatured,
	'recent'   => $orderRecent
	);

function add_quotes($var) {
	$var2 = '\'' . $var . '\'';
	return $var2;
}

?>